﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using MISAPP.Common;
using System.Data.Common;

namespace MISAPP.Classify.DataAccess
{

    public class DA_CIB001x:DA_Matrix
    {
        public DA_CIB001x()
        {     
            tableName = "企金";        
        }

        List<field> fieldList = null;
        protected static object obj = null;

        protected string CriteriaString = string.Empty;
        protected string GroupByString = string.Empty;
        protected string OrderByString = string.Empty;
        protected string OnString = string.Empty;
        /// <summary>
        /// 把group 的欄位放到select 上去，以方便看到明細
        /// </summary>
        protected string FieldString = string.Empty;
        
        public override string[] GetDistinctField()
        {
            return new string[]{"YYYYMM","風險等級"
                            ,"CUR","會計科目"
                            ,"區別","群組"
                            ,"利率調整型態"};
        }

        public DataTable GetGroupBy()
        {
            return getData("SELECT * FROM VIEW_CIB_GROUPBY ", "VIEW_CIB_GROUPBY");
        }

        public DataTable GetOrderBy()
        {
            return getData("SELECT * FROM VIEW_CIB_ORDERBY ", "VIEW_CIB_ORDERBY");
        }

        /// <summary>
        /// 取回加總的資料
        /// </summary>
        /// <param name="p_criteriaList"></param>
        /// <param name="p_groupBy"></param>
        /// <param name="p_orderBy"></param>
        /// <param name="p_checkAccount">是否排除公庫</param>
        /// <returns></returns>
        public DataTable GetSummaryData(Dictionary<string, List<string>> p_criteriaList
                                , string p_groupBy
                                , bool p_checkAccount
                                )
        {
            string sql = GetSummarySqlStatement();

            FillTableSchema(tableName);
            GetCriteriaString(p_criteriaList);
            GetGroupByString(p_groupBy);
            GetOnString();

            sql = string.Format(sql
                            ,CriteriaString
                            ,GroupByString
                            ,OnString
                            ,GroupByString
                            ,FieldString
                            ,GetExclusiveOfAccount(p_checkAccount)
                          );

            AddParameter(sql);
            return getData();
            
        }

        void AddParameter(string p_sql)
        {
            setCommand(p_sql);
            foreach (field fld in fieldList)
            {
                cmd.Parameters.Add(DAL.GetParameter(fld.ParameterName, fld.dbType, fld.fieldValue, cmd.CreateParameter()));
            }
        }

        public DataTable GetDetailData(Dictionary<string, string> p_selectKey,            
            Dictionary<string, List<string>> p_criteriaList,bool p_checkAccount)
        {
            string sql, selectKey;
           
            sql = GetDetailSqlStatement();
            FillTableSchema(tableName);
            GetCriteriaString(p_criteriaList);
            selectKey = GetCriteriaString2(p_selectKey);
            sql = string.Format(sql
                    , CriteriaString
                    , selectKey
                    , GetExclusiveOfAccount(p_checkAccount)
                    , OrderByString                    
                    );
            AddParameter(sql);
            return getData();
        }

        protected string GetCriteriaString2(Dictionary<string, string> p_selectKey)
        {
            OrderByString = string.Empty;
            StringBuilder sb = new StringBuilder();
            StringBuilder ordSB = new StringBuilder();
            foreach (KeyValuePair<string, string> key in p_selectKey)
            {
                ordSB.AppendFormat(string.Format(",{0}",key.Key.ToString()));
                sb.AppendFormat(" AND {0}=@{0} ",key.Key.ToString());
                fieldList.Add(new field("@" + key.Key.ToString()
                    ,GetFieldDbType(key.Key.ToString())
                    ,key.Value.ToString()));
            }
            OrderByString = string.Format(" ORDER BY {0} ", ordSB.ToString().Substring(1));
            return sb.ToString();
        }

        protected string GetSummarySqlStatement()
        {
            return @"
                    SELECT * FROM (

                    SELECT ROW_NUMBER() OVER(ORDER BY {1})  AS SEQ,* 
                    FROM (

                        SELECT A.REC, ROUND(B.TOT_餘額,0) AS TOT_餘額
                                    , ROUND(B.TOT_BAL,0)  AS TOT_均額
	                                , ROUND(A.SUM_CALFTP_RATE/B.TOT_BAL,7) AS CALFTP_RATE
	                                , ROUND(A.SUM_CAL承作利率/B.TOT_BAL,7) AS CAL承作利率
	                                , ROUND(A.SUM_CAL加減碼/B.TOT_BAL,7) AS CAL加減碼 
                                    {4}  
                                FROM 
	                                (
		                                SELECT 
			                                COUNT(*) AS REC
			                                , SUM(FTP_RATE * 均額)  AS SUM_CALFTP_RATE
			                                , SUM(承作利率 * 均額) AS SUM_CAL承作利率
			                                , SUM(加減碼 * 均額)   AS SUM_CAL加減碼
			                                , {3}
		                                FROM 企金 
		                                WHERE 均額 > 0 
				                                 {0}
                                                 {5}
		                                GROUP BY {1}
                                        
	                                ) A INNER JOIN (
					                                SELECT 
						                                SUM(餘額) AS TOT_餘額
						                                , SUM(均額) AS TOT_BAL
						                                , {3}						                            
					                                FROM 企金 
					                                WHERE 均額 > 0 
						                                {0}
                                                        {5}
					                                GROUP BY {1}                                                    
					                                )B
		                                ON {2}
		                            )A)A
                                    WHERE 1=1 
";
        }

        protected string GetDetailSqlStatement()
        {
            return @"
                    SELECT ROW_NUMBER() OVER( {3} )  AS SEQ,*
                    FROM(
                            SELECT [YYYYMM]
                                      ,[帳號]
                                      ,ISNULL([透支帳號],'') AS [透支帳號]
                                      ,[指標利率]
                                      ,[利率說明]
                                      ,[均額]
                                      ,[餘額]
                                      ,[CUR]
                                      ,[承作利率]
                                      ,[FTP_RATE]
                                      ,[利差]
                                      ,[LNAC_NAME]
                                      ,[LNAC_ID]
                                      ,[利率調整型態]
                                      ,[專案代碼]
                                      ,[專案名稱]
                                      ,[加減碼]
                                      ,[起息日]
                                      ,[到期日]
                                      ,[信用評等]
                                      ,[信用評分]
                                      ,[風險等級]
                                      ,[評等類別]
                                      ,[授信行業別代碼]
                                      ,[授信行業別]
                                      ,[核准額度]
                                      ,[調息週期]
                                      ,[循環別]
                                      ,[擔保品鑑價值]
                                      ,[擔保品代碼1]
                                      ,[擔保品名稱1]
                                      ,ISNULL([擔保品代碼2],'') AS [擔保品代碼2]
                                      ,ISNULL([擔保品名稱2],'') AS [擔保品名稱2]
                                      ,ISNULL([違約狀態],'') AS [違約狀態]
                                      ,[會計科目]
                                      ,[科目名稱]
                                      ,ISNULL([本金逾期代號],'') AS [本金逾期代號]
                                      ,[本金逾期金額]
                                      ,ISNULL([利息逾期代號],'') AS [利息逾期代號]
                                      ,[利息逾期金額]
                                  FROM [企金]
                                  WHERE 均額 > 0
                                    {0}
                                    {1}
                                    {2}
                                   
                        )A  {3}
";
        }

        /// <summary>
        /// 取得排除公庫
        /// </summary>
        /// <returns></returns>
        protected String GetExclusiveOfAccount(bool p_exclusiveOf)
        {
            return p_exclusiveOf == true ? " AND LEFT(帳號,3)<>'411' " : " AND 1=1 ";
        }

        /// <summary>
        /// 要先取到Group，才能在這Parse
        /// </summary>
        protected void GetOnString()
        {
            StringBuilder sb = new StringBuilder();
            if (GroupByString != string.Empty)
            {
                string[] ons = GroupByString.Split(',');
                foreach (string str in ons)
                {
                    sb.AppendFormat(",(A.{0} = B.{0})",str);
                }
            }
            OnString = sb.ToString().Substring(1).Replace(","," AND ") ;
        }

        protected void GetGroupByString(string p_groupBy)
        {
            GroupByString = p_groupBy;
            FieldString = ",A." + GroupByString.Replace(",", ",A.");
        }

        protected void GetOrderByString(string p_orderBy)
        {
            OrderByString = "";
            string tmp = p_orderBy;
            if (tmp != string.Empty)
            {
                OrderByString = string.Format(" ORDER BY {0}", tmp);
            }
        }

        protected string ParseDictionary(Dictionary<string, int> p_groupBy)
        {
            StringBuilder sb = new StringBuilder();
            foreach (KeyValuePair<string, int> itm in p_groupBy)
            {
                sb.AppendFormat(",{0}", itm.Key);
            }
            return sb.ToString().Length ==0 ? "" : sb.ToString().Substring(1);
        }

        protected void GetCriteriaString(Dictionary<string, List<string>> p_criteriaList)
        {
            fieldList = new List<field>();
            StringBuilder criteria = new StringBuilder();
            foreach (KeyValuePair<string, List<string>> itm in p_criteriaList)
            { 
                criteria.Append(ParseList2(itm.Key, itm.Value));                
            }
            CriteriaString = criteria.ToString();
        }

        protected string ParseList2(string p_fldName, List<string> p_list)
        {
            string result, inStr;
            inStr = "";
            List<string> lst = new List<string>();
            int x = 0;
            foreach (string str in p_list)
            {
                if (x > 0)
                {
                    lst.Add(str);
                }
                else
                {
                    inStr = str.Equals("YES") ? " NOT IN " : " IN ";
                }
                x++;
            }
            var valueArray = lst.ToArray();
            var parms = valueArray.Select((s, i) => string.Format("@{0}{1}" , p_fldName , i.ToString())).ToArray();
            var inclause = string.Join(",", parms);

            FillFieldList(valueArray, parms, p_fldName);

            result = string.Format(" AND {0} {2} ({1})"
                                        , p_fldName
                                        , inclause
                                        , inStr);
            return result;
        }

        protected void FillFieldList(string[] p_valueArray,string[] p_parmArray,string p_fieldName)
        {
            
            for (int i = 0; i < p_valueArray.Length; i++)
            {
                fieldList.Add(new field(p_parmArray[i], GetFieldDbType(p_fieldName), p_valueArray[i].ToString()));
            }
        }

        protected string ParseList(string p_fldName ,List<string> p_list)
        {
            int i = 0;
            StringBuilder sb = new StringBuilder();
            string result,inStr;
            result = "";
            inStr = " IN ";
            if (p_list.Count > 1)
            {
                foreach (string str in p_list)
                {
                    if (i == 0)
                    {
                        inStr = str.Equals("YES") ? " NOT IN " : " IN ";
                    }
                    else
                    {
                        sb.AppendFormat(",'{0}'", str);
                    }
                    i++;
                }
                if (sb.Length > 0)
                {
                    result = string.Format(@" AND {0}
                                                  {2} ({1})"
                                        , p_fldName
                                        , sb.ToString().Substring(1)
                                        , inStr);
                }                            
            }
            return result;
        }

        public static DA_CIB001x GetInstance()
        { 
            if(obj ==  null)
            {
                obj = InstanceHelper.CreateInstance("DA_CIB001");               
            }
            return obj as DA_CIB001x;
        }
    }
}